<?php
namespace Home\Controller;
use Think\Controller;

/**
 * 示例控制器
 * @author yukari
 * 2018-08-27
 */
class DemoController extends BaseController {

    /**
     * 注意：示例中包含删除操作，请不要直接运行!
     * @author yukari
     * 2018-08-27
     */
    public function _initialize(){

      	echo "示例中包含删除操作，请不要直接运行!";
      	exit();
    }

    /**
     * 输出页面
     * @author yukari
     * 2018-08-28
     */ 
    public function index(){

        $this->display();
    }

  	/**
  	 * ajax返回参数
  	 * @author yukari
  	 * 2018-08-27
  	 */  
  	public function backRes(){
    		$arr['id'] = "1";
    		$arr['name'] = "name";

    		$this->response($arr,0,"success");
  	}  

  	/**
  	 * 连接使用postgresql示例
  	 * @author yukari
  	 * 2018-08-27
  	 */
    public function pgSqlUse(){

      	$pgdb = new \Home\Model\PostgresqlModel();

      	/*查询*/
      	$sql = "select * from test_table";
      	$res = $pgdb->mpg_select($sql);
      	print_r($res);

      	/*插入*/
      	$id = "10";
      	$name = "yu;ka'ri";
      	//防sql注入，字符串转义
      	$name = pgsql_input_trans($name); //字符串输入
      	$id = pgsql_input_trans($id,true); //整数输入
      	$sql = "insert into test_table values ($id, $name)";
      	$res = $pgdb->mpg_insert($sql);
      	print_r($res);
    }

    /**
  	 * 解析excel
  	 * @author yukari
  	 * 2018-08-27
  	 */
   	public function getExcelContent(){

        $excel_path = "Updata/Excel/2018-08-27/demo.xlsx";
        $type = "xlsx";

        //解析excel
        Vendor('PHPexcel.PHPExcel');
        if(strtolower ( $type )=='xls'){  
    
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');  
        }elseif(strtolower ( $type )=='xlsx'){  
            
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');  
        } 
        $objReader->setReadDataOnly(true); 
        $objPHPExcel = $objReader->load($excel_path);

        //解析sheet1的数据，若包含多个sheet，使用getSheetCount获取数量再循环
        $objPHPExcel->setActiveSheetIndex(0);
        $le_num = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];

       	//获取最大行数
        $excel_all_row = $objPHPExcel->getActiveSheet()->getHighestRow();

        //获得最大列数
        $excel_all_column = $objPHPExcel->getActiveSheet()->getHighestColumn();
        $excel_all_column = ord($excel_all_column) - ord('A');

        //注意第一行是1，不是0
        for($i = 1;$i <= $excel_all_row;$i++ ){
        	for($j = 0;$j <= $excel_all_column;$j++){
        		//获取单元格的数据
        		$arr[$le_num[$j].$i] = $objPHPExcel->getActiveSheet()->getCell($le_num[$j].$i)->getValue();
        		
        	}
        }
        print_r($arr);
        
   	}

    /**
  	 * 下载excel
  	 * @author yukari
  	 * 2018-08-27
  	 */
   	public function downloadExcel(){

        Vendor('phpexcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties()->setTitle("EXCEL导出示例");

        //在sheet 0中添加数据
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', "id")    
            ->setCellValue('B1', "name")
            ->setCellValue('C1', "pid"); 
       	$objPHPExcel->getActiveSheet()->setTitle('测试sheet');
       	$objPHPExcel->setActiveSheetIndex(0);

       	//清空缓冲区，避免乱码
       	ob_end_clean();

       	//xlsx文件
       	header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    		header('Content-Disposition: attachment;filename="'.'测试'.'.xlsx"');
    		header('Cache-Control: max-age=0');
    		$objWriter=\PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');

    		//xls文件
    		/*
    		header('Content-Type: application/vnd.ms-excel');
    		header('Content-Disposition: attachment;filename="'.'测试'.'.xls"');
    		header('Cache-Control: max-age=0');
    		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    		*/
        $objWriter->save('php://output');
        exit;        	
   	}

    /**
     * 连接使用Elasticsearch示例
     * @author yukari
     * 2018-08-28
     */
    public function elasticsearchUse(){

        $esdb = new \Home\Model\ElasticsearchModel();

        //创建索引
        $params = [
            'index' => 'yukari_test', //索引名称
            'body' => [
                'settings'=> [ //配置
                    'number_of_shards'=> 3,//主分片数
                    'number_of_replicas'=> 1 //主分片的副本数
                ],
                'mappings'=> [  //映射
                    '_default_' => [ //默认配置，每个类型缺省的配置使用默认配置
                        '_all'=>[   //  关闭所有字段的检索
                            'enabled' => 'false'
                        ],
                        '_source'=>[   //  存储原始文档
                            'enabled' => 'true'
                        ],
                        'properties'=> [ //配置数据结构与类型
                            'name'=> [ //字段1
                                'type'=>'string',//类型 string、integer、float、double、boolean、date
                                'index'=> 'analyzed',//索引是否精确值  analyzed not_analyzed
                            ],
                            'age'=> [ //字段2
                                'type'=>'integer',
                            ],
                            'sex'=> [ //字段3
                                'type'=>'string',
                                'index'=> 'not_analyzed', 
                            ],
                        ]
                    ],
                    'my_type' => [ 
                        'properties' => [
                            'phone'=> [ 
                                'properties' => [
                                    'fp'=> [ 
                                        'type'=>'string',
                                    ],
                                    'lp'=> [ 
                                        'type'=>'string',
                                    ],                                    
                                ]
                                
                            ],                           
                        ]
                    ],
                ],
            ]
        ];
        $res = $esdb->create_index($params);

        //删除索引
        $res = $esdb->delete_index("yukari_test");

        //插入单条数据
        $params = [
            'index' => 'yukari_test',
            'type' => 'my_type',
            'body' => [ 
                'age' => 17,
                'name' => 'saki',
                'sex' => '女性',
                'idcard' => 1129,
                'phone' => [
                    'fp'=>'124',
                    'lp'=>'987',
                ]
            ]
        ];
        $res = $esdb->post_single_doc($params);

        //根据id获取文档
        $res = $esdb->get_doc_by_id('yukari_test','my_type','AWV_R6kTRTUGEZwQICxs');

        //根据id更新文档
        $params = [
            'index' => 'yukari_test',
            'type' => 'my_type',
            'id' => 'AWV_R6kTRTUGEZwQICxs',
            'body' => [
                'doc' => [ //将doc中的文档与现有文档合并
                    'phone' => [
                        'lp' => 'yo'
                    ]
                    
                ]
            ]
        ];
        $res = $esdb->update_doc_by_id($params);

        //根据id删除文档
        $res = $esdb->delete_doc_by_id('yukari_test','my_type','AWV_R6kTRTUGEZwQICxs');
        print_r($res);
    }

    /**
     * 连接使用Neo4j示例
     * @author yukari
     * 2018-08-28
     */
    public function neo4jUse(){

        $neodb = new \Home\Model\Neo4jModel();

        //查询节点、关系、属性并解析
        $cypher = 'MATCH (m:person{name:"suika"})-[r]-(n) RETURN m,r,n';
        $res = $neodb->run($cypher);
        foreach ($res->getRecords() as $r) {
            //获取节点标签
            print_r($r->get('n')->labels());
            //获取节点所有属性的值
            print_r($r->get('n')->values());
            //获取节点所有属性的key
            print_r($r->get('n')->keys());
            //获取节点某个属性的值,不存在则返回空
            print_r($r->get('n')->value("uid"));
            //获取关系类型
            print_r($r->get('r')->type());
            //获取关系属性
            print_r($r->get('r')->value('time'));exit();
        }
    }
}